﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Xml;
using System.Data.SqlClient;


namespace LicPdaService
{
    public class PDAXmlIntoMoveProcess
    {
        /// <summary>
        /// 7.1库位转移  Add By Quinn.Ling 201703
        /// DataSource:Inventory，InvMove
        /// </summary>
        public static string GetPDAIntoMoveData(XmlDocument doc)
        {
            #region 通用变量定义
            string docResult = string.Empty; //返回值
            string userid = string.Empty;
            string whseid = string.Empty;
            string customer_code = string.Empty;
            string sku = string.Empty;
            string batch = string.Empty;
            string loc = string.Empty;
            string qty = "0";
            string qty_new = string.Empty;
            string lot = string.Empty;
            string lpn = string.Empty;
            string lpn_new = string.Empty;
            string loc_new = string.Empty;
            string return_status = "";
            string skudescr = string.Empty;
            string uom = string.Empty;
            List<string> sqllists = new List<string>();
            string strSql = string.Empty;
            string lottable01 = string.Empty;

            DataTable ldt = App.Xml2DataTable(doc.InnerXml);
            ldt.Columns.Add("lpn_new");
            XmlDocument docreturn = new XmlDocument();//返回报文
            XmlDeclaration dec = docreturn.CreateXmlDeclaration("1.0", "utf-8", null);//建立Xml的定义声明  
            docreturn.AppendChild(dec);
            XmlElement rootResult = docreturn.CreateElement("NewDataSet"); //创建根节点
            #endregion

            try
            {
                #region 参数源
                userid = ldt.Rows[0]["userid"].ToString();
                whseid = ldt.Rows[0]["whseid"].ToString();
                //App.ws = new Fcl.WSOP.sqldbop();
                //App.ws.Init(System.Configuration.ConfigurationManager.ConnectionStrings["adpwmsConnectionString"].ConnectionString);
                //XmlNode root = doc.DocumentElement; // 报文根节点
                //XmlNodeList rootlist = root.ChildNodes;
                //排除重复项
                DataTable ldt1 = ldt;
                for (int i = ldt1.Rows.Count - 2; i > 0; i--)
                {
                    DataRow[] ldrs = ldt1.Select("sku='" + ldt1.Rows[i]["sku"].ToString() + "'");
                    if (ldrs.Length > 1)
                    {
                        ldt1.Rows.RemoveAt(i);
                    }
                }
                ldt1.AcceptChanges();
                for (int i = 0; i < ldt1.Rows.Count; i++)
                {
                    //获取新库位-旧逻辑
                    //lpn_new = App.ws.ExecScalar("DECLARE @key varchar(50) exec pro_GetLpn '" + whseid + "','PLT','托盘号',@key OUTPUT select @key as lpn ", "").ToString();
                    //ldt1.Rows[i]["lpn_new"] = lpn_new;

                    strSql = "DECLARE @key varchar(50) exec pro_GetLpn '" + whseid + "','PLT','托盘号',@key OUTPUT select @key as lpn";
                    DataTable dtPro = App.GetDataTable(App.DBconWMS, strSql, "Sku");
                    if (dtPro.Rows.Count > 0)
                    {
                        ldt1.Rows[i]["lpn_new"] = dtPro.Rows[0]["lpn"].ToString();
                    }
                }

                sqllists.Clear();
                string movekey = string.Empty;
             
                //库位转移表
                //string movekey = App.ws.ExecScalar("DECLARE @key varchar(50) exec pro_GetOrderNo '" + whseid + "','LT','MOVEKEY','库位转移单号',@key OUTPUT select @key as movekey ", "").ToString();//调用存储过程pro_GetOrderNo生成转移单号
                strSql = "DECLARE @key varchar(50) exec pro_GetOrderNo '" + whseid + "','LT','MOVEKEY','库位转移单号',@key OUTPUT select @key as movekey";
                DataTable dtOrd = App.GetDataTable(App.DBconWMS, strSql, "Sku");
                if (dtOrd.Rows.Count > 0)
                {
                    movekey = dtOrd.Rows[0]["movekey"].ToString();
                }

                strSql = "insert into InvMove(whseid,movekey,status,pre_date,addwho,adddate,c_who,c_date,movereason) values ";
                strSql += "('" + whseid + "','" + movekey + "','1','" + DateTime.Now + "','" + userid + "','" + DateTime.Now + "','" + userid + "','" + DateTime.Now + "','PDA库位转移')";
                sqllists.Add(strSql);
                #endregion

                for (int i = 0; i < ldt.Rows.Count; i++)
                {
                    DataRow[] ldrs = ldt1.Select("sku='" + ldt.Rows[i]["sku"].ToString() + "'");
                    if (ldrs.Length>0)
                    {
                        lpn_new = ldrs[0]["lpn_new"].ToString();
                    }
                    #region 参数源
                    //XmlNode nodeOrder = rootlist[i];
                    try
                    {
                       
                        customer_code = ldt.Rows[i]["customer_code"].ToString();
                        sku =ldt.Rows[i]["sku"].ToString();
                        batch=ldt.Rows[i]["batch"].ToString();
                        loc = ldt.Rows[i]["loc"].ToString();
                        qty_new =ldt.Rows[i]["qty"].ToString();
                        lot = ldt.Rows[i]["lot"].ToString();
                        lpn = ldt.Rows[i]["lpn"].ToString();
                        loc_new =ldt.Rows[i]["loc_new"].ToString();

                        strSql = "SELECT description,unit,customer_code FROM Sku WHERE sku='" + sku + "' and whseid='" + whseid + "'";
                        DataTable dt = App.GetDataTable(App.DBconWMS, strSql, "Sku");
                        if (dt.Rows.Count > 0)
                        {
                            skudescr = dt.Rows[0]["description"].ToString();
                            uom = dt.Rows[0]["unit"].ToString();
                            customer_code = dt.Rows[0]["customer_code"].ToString();
                        }

                    }
                    catch (Exception e)
                    {
                        return_status = "报文读取失败" + e.Message.ToString();
                    }
                    #endregion

                    #region 数据存储
                    try
                    {
                        //同库位间不可转移
                        if (loc == loc_new)
                        {
                            return_status = "库位转移执行失败！！！原因：源库位 "+loc+" 和目标库位 "+loc_new+" 相同，系统设定不可在同库位间执行库位转移！！！";
                            App.Log("PDAXmlIntoMoveProcess", return_status);
                        }

                        //库位转移前 判定库位合法性
                        strSql = "SELECT loc FROM [dbo].[Location] where loc='" + loc + "'  AND whseid='" + whseid + "'";
                        DataTable dtloc = App.GetDataTable(App.DBconWMS, strSql, "LOC");
                        if (dtloc.Rows.Count <= 0)
                        {
                            return_status = "上架失败-库位不合法导致！！！ 可能原因：1、旧库位"+loc+ "无效  2、扫描库位不隶属登录账号所在的厂别"+whseid;
                            App.Log("PDAXmlIntoMoveProcess", strSql);
                        }
                        else
                        {
                            strSql = "SELECT loc FROM [dbo].[Location] where loc='" + loc_new + "'  AND whseid='" + whseid + "'";
                            DataTable dtlocnew = App.GetDataTable(App.DBconWMS, strSql, "LOC");
                            if (dtlocnew.Rows.Count <= 0)
                            {
                                return_status = "上架失败-库位不合法导致！！！ 可能原因：1、新库位"+ loc_new + "无效  2、扫描库位不隶属登录账号所在的厂别"+whseid;
                                App.Log("PDAXmlIntoMoveProcess", strSql);
                            }
                            else
                            {
                                #region 执行 库位转移
                                strSql = "SELECT I.customer_code,I.sku,L.lottable01,I.loc,I.lot,I.lpn,qty  ";
                                strSql += "FROM Inventory AS I,LotAttribute AS L WHERE I.lot=L.lot AND I.sku='" + sku + "' and I.lpn='" + lpn + "' and I.lot='"+lot+"' and I.whseid='" + whseid + "' and (I.qty-I.allocated_qty-I.picked_qty)>0 and I.status='OK' ";
                                DataTable dti = App.GetDataTable(App.DBconWMS, strSql, "Sku");
                                if (dti.Rows.Count > 0)
                                {
                                    lottable01 = dti.Rows[0]["lottable01"].ToString();
                                    qty = dti.Rows[0]["qty"].ToString();

                                    if (Convert.ToDecimal(qty) >= Convert.ToDecimal(qty_new))
                                    {
                                        strSql = "insert into InvMoveDetail(whseid,movekey,line_no,customer_code,sku,skudescr,lottable01,loc,lot,lpn,qty,toloc,tolpn,toqty,uom,addwho,adddate) values ";
                                        strSql += "('" + whseid + "','" + movekey + "','" + (i + 1).ToString().PadLeft(5, '0') + "','" + customer_code + "','" + sku + "','" + skudescr + "','" + lottable01 + "','" + loc + "','" + lot + "','" + lpn + "'," + qty + "";
                                        strSql += ",'" + loc_new + "','" + lpn_new + "'," + qty_new + ",'" + uom + "','" + userid + "','" + DateTime.Now + "')";
                                        sqllists.Add(strSql);

                                        //库存明细表--更新旧库位数据，新增新库位数据
                                        strSql = "update inventory set qty=qty-" + qty_new + "";
                                        strSql += " where whseid='" + whseid + "' and customer_code='" + customer_code + "' and sku='" + sku + "' and loc='" + loc + "' and lpn='" + lpn + "' and lot='" + lot + "';";
                                        sqllists.Add(strSql);


                                        string status = GetStatus(whseid, customer_code, sku, lot, loc, lpn);
                                        strSql = "insert into inventory(whseid,customer_code,sku,skudescr,loc,lot,lpn,status,qty,uom) values ";
                                        strSql += "('" + whseid + "','" + customer_code + "','" + sku + "','" + skudescr + "','" + loc_new + "','" + lot + "','" + lpn_new + "','OK'," + qty_new + ",'" + uom + "');";
                                        sqllists.Add(strSql);
                                    }
                                }
                                else
                                {
                                    // 未确认入库前（上架入账完成确认前）
                                    strSql = "SELECT SUM(check_qty) AS qty FROM [LicWMS].[dbo].[ReceiptDetail] WHERE lpn='" + lpn + "' and sku='" + sku + "' and loc='" + loc + "'  and whseid='" + whseid + "'";
                                    DataTable dtr = App.GetDataTable(App.DBconWMS, strSql, "ReceiptDetail");
                                    if (dtr.Rows.Count > 0)
                                    {
                                        qty = dtr.Rows[0]["qty"].ToString();
                                        strSql = "insert into InvMoveDetail(whseid,movekey,line_no,customer_code,sku,skudescr,lottable01,loc,lot,lpn,qty,toloc,tolpn,toqty,uom,addwho,adddate,data_from) values ";
                                        strSql += "('" + whseid + "','" + movekey + "','" + (i + 1).ToString().PadLeft(5, '0') + "','" + customer_code + "','" + sku + "','" + skudescr + "','" + lottable01 + "','" + loc + "','" + lot + "','" + lpn + "'," + qty + "";
                                        strSql += ",'" + loc_new + "','" + lpn_new + "'," + qty_new + ",'" + uom + "','" + userid + "','" + DateTime.Now + "','PDA上架完成前分配库位')";
                                        sqllists.Add(strSql);

                                        strSql = "UPDATE [LicWMS].[dbo].[ReceiptDetail] SET loc='" + loc_new + "',lpn='" + lpn_new + "' WHERE  lpn='" + lpn + "' and sku='" + sku + "' and loc='" + loc + "'  and whseid='" + whseid + "'";
                                        sqllists.Add(strSql);
                                    }
                                    else
                                    {
                                        return_status = "新库位分配失败，原因：PDA未执行 到货验收&到货打印";
                                        App.Log("PDAXmlIntoMoveProcess", strSql);
                                    }
                                }
                                #endregion
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        return_status = "数据更新失败" + ex.Message.ToString();
                        App.Log("PDAXmlIntoMoveProcess", ex.ToString());
                    }
                    #endregion
                }
                if (return_status=="")
                {
                    App.ExecuteSQLScriptLists(sqllists, App.DBconWMS);
                }
               

                #region 构建XML报文

                for (int i = 0; i <ldt1.Rows.Count; i++)
                {
                    XmlNode order_result = docreturn.CreateElement("move_return");

                    XmlElement useridResult = docreturn.CreateElement("userid");
                    useridResult.InnerText = userid;
                    order_result.AppendChild(useridResult);

                    XmlElement whseidResult = docreturn.CreateElement("whseid");
                    whseidResult.InnerText = whseid;
                    order_result.AppendChild(whseidResult);

                    XmlElement skuResult = docreturn.CreateElement("sku");
                    skuResult.InnerText = ldt1.Rows[i]["sku"].ToString();
                    order_result.AppendChild(skuResult);

                    XmlElement batchResult = docreturn.CreateElement("batch");
                    batchResult.InnerText = ldt1.Rows[i]["batch"].ToString();
                    order_result.AppendChild(batchResult);

                    XmlElement qtyResult = docreturn.CreateElement("qty");
                    qtyResult.InnerText = ldt1.Rows[i]["qty"].ToString();
                    order_result.AppendChild(qtyResult);

                    XmlElement storageResult = docreturn.CreateElement("storage");
                    storageResult.InnerText = ldt1.Rows[i]["loc_new"].ToString();
                    order_result.AppendChild(storageResult);

                    XmlElement lpnResult = docreturn.CreateElement("lpn");
                    lpnResult.InnerText = ldt1.Rows[i]["lpn_new"].ToString();
                    order_result.AppendChild(lpnResult);

                    XmlElement return_statusResult = docreturn.CreateElement("return_status");
                    return_statusResult.InnerText = return_status;
                    order_result.AppendChild(return_statusResult);

                    rootResult.AppendChild(order_result);
                }
             
                #endregion
            }
            catch (Exception ex)
            {
                App.Log("PDAXmlIntoMoveProcess.cs", ex.Message.ToString());
            }

            docreturn.AppendChild(rootResult);
            return docreturn.InnerXml;
        }

        #region 库位状态
        public static string GetStatus(string whseid, string customer_code, string sku, string lot, string loc, string lpn)
        {
            string lsreturn="";
            string strSql = "select status from inventory where whseid='" + whseid + "' and customer_code='" + customer_code + "' and sku='" + sku + "'";
            strSql += " and lot='" + lot + "' and loc='" + loc + "' and lpn='" + lpn + "'";
            DataTable ldt_status = App.GetDataTable(App.DBconWMS, strSql, "invent");
            if (ldt_status.Rows.Count>0)
            {
                lsreturn = ldt_status.Rows[0]["status"].ToString();
            }
            return lsreturn;
        }
        #endregion
    }
}
